<?php
namespace App\Admin\Controllers;
use Encore\Admin\Layout\Content;
use Encore\Admin\Facades\Admin;
use App\Models\Class_section_group;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use  App\Admin\Controllers\Common\ReportBase;
class Report_dashboardController extends ReportBase {
	public function __construct() {
		$term = DB::table("terms")->orderBy("id",'desc')->first();
		parent::__construct($term->year,$term->season);
	}
	/**
	 * [Dashboard]
	 */
	public function index(Content $content) {
		return Admin::content(function (Content $content) {
            $content->header(trans('Dashboard'));
            $content->description(trans('仪表盘'));
			$content->body(view("admin.dashboard.index",["Week"=>$this->term]));
        });
	}

	/**
	 * [获取初始数据]
	 */
	public function getInitData(Request $request) {
		// 默认选中班级数据
		$checked_track = [];
		$checked_contrast = [];
		$checked_ids = DB::table("dashboard_checked_squads")
			->where("admin_id",'=',Admin::user()->id)
			->first(['track_ids','contrast_ids']);
		if($checked_ids){
			$checked_track_ids = array_filter(explode(",",$checked_ids->track_ids));
			$checked_contrast_ids = array_filter(explode(",",$checked_ids->contrast_ids));
			foreach($checked_track_ids as $id){
				$checked_track[] = $this->getTrackRow($id);
			}
			foreach($checked_contrast_ids as $id){
				$checked_contrast[] = $this->getTrackRow($id);
			}
		}
		// 联动数据
		$class_stage = $request->get("class_stage");// 班段
		$class_group = $request->get("class_group");// 分组
		$class_type = $request->get("class_type"); // 班型
		$year = $request->get("year"); // 开办段
		$campus = $request->get("campuses");// 校区,

		//根据班段获取分组
		$where_stage = [['type',"=","2"]];
		if($class_stage){
			$where_stage[] = ['pid',"=",$class_stage];
		}

		$data = [
			"class_stage" => Class_section_group::regulate_options(),
			"class_type" => DB::table("class_types")->select(['id','name'])->get(),
			"class_group" => DB::table("class_section_groups")
								->where($where_stage)
								->select(['id','name'])->get(),
			"campuses" => DB::table("campuses")->select(['id','name'])->get(),
			"checked_track" => $checked_track,
			"checked_contrast" => $checked_contrast
		];
		// 查出班号
		$db_classes = DB::table('squads');
		if($class_group){
			$db_classes = $db_classes->where('group',"=",$class_group);
		}
		if($class_stage){
			$db_classes = $db_classes->where('section',"=",$class_stage);
		}
		if($year){
			$db_classes = $db_classes->where('start_year',"=",$year);
		}
		if($campus){
			$db_classes = $db_classes->where('campus',"=",$campus);
		}
		if($class_type){
			$db_classes = $db_classes->where('type',"=",$class_type);
		}
		$data['classes'] = $db_classes->get(["id","name"]);
		return ['code'=>1,'msg'=>'ok','data'=>$data];
	}

	/**
	 * [根据squads.id 制作一行]
	 */
	public function mkRowById(Request $requset) {
		$data = $this->getTrackRow($requset->get('id'));
		$field = $requset->get("field");
		$check = DB::table("dashboard_checked_squads")
			->where("admin_id",'=',Admin::user()->id)
			->first();
		$check = json_decode(json_encode($check),true);
		if($check){
			$arr = explode(",",$check[$field]);
			$arr[] = $requset->get('id');
			$arr = array_merge(array_filter(array_unique($arr)));
			DB::table("dashboard_checked_squads")
				->where("admin_id",'=',Admin::user()->id)
				->update([$field=>implode(",",$arr)]);
		}else{
			DB::table("dashboard_checked_squads")->insert([
				'admin_id' => Admin::user()->id,
				$field => $requset->get('id')
			]);
		}
		return ['code'=>1,'msg'=>'ok','data'=>$data];
	}
	private function getTrackRow($id) {
		$data = DB::table("squads as squad")
			->join('admin_users as teacher','squad.class_teacher','=','teacher.id')
			->where('squad.id',"=",$id)
			->select([
				"squad.id","squad.full_name","teacher.username as teacher",
				'squad.start_year','squad.start_season','squad.name'
			])->first();
		$data = json_decode(json_encode($data), true);
		if($data['full_name']){
			$data['level'] = explode("(",$data['full_name'])[1];
			$data['stage'] = explode("-",$data['level'])[1];
			$data['stage'] = mb_substr($data['stage'],0,-1);
			$data['level'] = explode("-",$data['level'])[0];
			$data['start'] = $data['start_year']."-".$data['start_season'];
		}
		return $data;
	}

	/**
	 * [根据id删除一行]
	 */
	public function delRowById(Request $request) {
		$id = $request->get("id");
		$field = $request->get('field');
		DB::beginTransaction();
		try {
			$ids = DB::table("dashboard_checked_squads")
				->where("admin_id",'=',Admin::user()->id)
				->value($field);
			$ids = explode(",",$ids);
			for($i=0;$i<sizeof($ids);$i++){
				if($id == $ids[$i]){
					unset($ids[$i]);break;
				}
			}
			DB::table("dashboard_checked_squads")
				->where("admin_id",'=',Admin::user()->id)
				->update([$field=>implode(",",$ids)]);
			DB::commit();
			return ['code'=>1,'msg'=>'删除成功','data'=>''];
		} catch (\Exception $e) {
			DB::rollback();
			return ['code'=>0,'msg'=>'删除失败，请稍后...'.$e->getMessage(),'data'=>''];
		}
	}


	/**
	 * [获取班级情况数据]
	 */
	public function getSituationTable() {
		date("w") == 0 ? $today_week = 7 : $today_week = date("w");
		$today_week - 1 == 0 ? $yesterday_week = 7 : $yesterday_week = $today_week - 1;
		$lesson_y = DB::table("crousesheets")
					->where('status','=','1')
					->where("section","=",$yesterday_week)->pluck("squads_id");
		$lesson_t =  DB::table("crousesheets")
					->where('status','=','1')
					->where("section","=",$today_week)->pluck("squads_id");
		$student_y = DB::table("students")->whereIn("squad_id",$lesson_y)->where('status','=','1')->count();
		$student_t = DB::table("students")->whereIn("squad_id",$lesson_t)->where('status','=','1')->count();

		$attendance_y = DB::table("attendance_records")->where("attendance_status","=","1")
			->whereBetween("created_at",[
				date("Y-m-d",strtotime("-1 days"))." 00:00:00",
				date("Y-m-d",time())." 00:00:00"
			])->count();
		$attendance_y_c = DB::table("attendance_records")
			->whereBetween("created_at",[
				date("Y-m-d",strtotime("-1 days"))." 00:00:00",
				date("Y-m-d",time())." 00:00:00"
			])->count();
		$unattendance_y = DB::table("notices as notice")
					->join("squads as squad","notice.squad_id","=","squad.id")
					->join('admin_users as teacher','notice.tutor_id',"=","teacher.id")
					->where([["notice.status","=","1"],["notice.notice_type","=","1"]])
					->whereBetween("notice.starttime",[
						date("Y-m-d",strtotime("-1 days"))." 00:00:00",
						date("Y-m-d",time())." 00:00:00"
					])->select([
						"squad.name as classes","teacher.username as teacher"
					])->get();
		$unattendance_y = json_decode(json_encode($unattendance_y), true);
		foreach($unattendance_y as &$v){
			$v = implode("-",$v);
		}
		$unattendance_y ? $unattendance_y = implode('，',$unattendance_y) : $unattendance_y = "无";
		$data = [[
			'col' => "课程：",
			'yesterday' => sizeof($lesson_y)."节",
			'today' => sizeof($lesson_t)."节"
		],[
			'col' => "上课学生数量：",
			'yesterday' => $student_y."人",
			'today' => $student_t."人"
		],[
			'col' => "出勤：",
			'yesterday' => $attendance_y."人次",
			'today' => ""
		],[
			'col' => "整体出勤率：",
			'yesterday' => "",
			'today' => ""
		],[
			'col' => "出勤Last3班级：",
			'yesterday' => $this->getLast3Attendance(),
			'today' => ""
		],[
			'col' => "未打考勤班级：",
			'yesterday' => $unattendance_y,
			'today' => ""
		]];
		if($attendance_y_c == 0){
			$data[3]['yesterday'] = "- / -";
		}else{
			$data[3]['yesterday'] = round($attendance_y / $attendance_y_c * 100,2)."%";
		}
		return ['code'=>1,'msg'=>'ok','data'=>$data];
	}
	private function getLast3Attendance() {
		$arr_1 = DB::table("attendance_records as a")
			->join("students as st","a.student_id","=","st.id")
			->join('squads as sq','sq.id','=','st.squad_id')
			->where('a.attendance_status','=','1')
			->whereBetween("a.created_at",[
				date("Y-m-d",strtotime("-1 days"))." 00:00:00",
				date("Y-m-d",time())." 00:00:00"
			])->select(DB::raw('count(1) as count,st.squad_id,sq.name'))
			->groupBy("st.squad_id")->orderBy("st.squad_id",'asc')->get();
		$arr_1 = json_decode(json_encode($arr_1), true);
		$arr_0 = DB::table("attendance_records as a")
			->join("students as st","a.student_id","=","st.id")
			->join('squads as sq','sq.id','=','st.squad_id')
			->whereBetween("a.created_at",[
				date("Y-m-d",strtotime("-1 days"))." 00:00:00",
				date("Y-m-d",time())." 00:00:00"
			])->select(DB::raw('count(1) as count,st.squad_id,sq.name'))
			->groupBy("st.squad_id")->orderBy("st.squad_id",'asc')->get();
		$arr_0 = json_decode(json_encode($arr_0), true);
		$res = [];
		foreach($arr_0 as $k => $v){
			if($v['count'] == 0){
				$rate = "- / -";
			}else{
				$rate = round($arr_1[$k]['count'] / $v['count'],4)*100;
			}
			$res[] = [
				'rate' => $rate,
				'name' => $v['name']
			];
		}
		$sortBy = array_column($res,'rate');
		array_multisort($sortBy,SORT_ASC,$res);
		$sorted = [];
		if(sizeof($res)<3){
			foreach($res as $v){
				$sorted[] = $res[$i]['name']." - ".$res[$i]['rate']."%";
			}
		}else{
			for($i=0;$i<3;$i++){
				$sorted[] = $res[$i]['name']." - ".$res[$i]['rate']."%";
			}
		}
		return implode(",",$sorted);
	}

	/**
	 * [周流失情况(近三周)]
	 */
	public function getWeekLossTable() {
		$start_week = $this->term - 4;
		$end_week = $this->term - 1;
		$reportLossCtrl = new Report_lossController();
 		return ['code'=>1,'msg'=>'ok','data'=>$reportLossCtrl->mkWeekLossArr($start_week,$end_week)];
	}

	/**
	 * [日流失情况]
	 */
	public function getTodayLossTable(Request $request) {
		$date_arr = [$request['date']." 00:00:00",$request['date']." 23:59:59"];
		$limit = $request->get("pageSize");
		$pageNumber = $request->get("pageNumber");
		$offset = ($pageNumber - 1) * $limit;
		$lost_squad_ids = DB::table("applys")
			->whereIn('type',["2","3"])
			->where('status','=','1')
			->whereBetween('sp_time',$date_arr)
			->pluck('squad_id');
		$lost_squad_ids = array_merge(array_filter(array_unique(json_decode(json_encode($lost_squad_ids),true))));
		$data = DB::table("squads as squad")
			->join('admin_users as teacher','squad.class_teacher','=','teacher.id')
			->join('class_section_groups as section','squad.section','=','section.id')
			->whereIn('squad.id',$lost_squad_ids)
			->orderBy("teacher.id")->orderBy("squad.id")
			->select([
				'squad.class_teacher as tutor_id',
				"teacher.username as tutor",
				"squad.name as squad_name",
				"squad.id as squad_id",
				"squad.start_year as year",
				"squad.start_date as date",
				"section.name as section_name",
			])->limit($limit)->offset($offset)->get();
		$data = json_decode(json_encode($data), true);
		foreach($data as &$v){
			$v['month'] = date("m",strtotime($v['date']));
			$sheets = DB::table("crousesheets as sheet")
				->join("admin_users as teacher",'sheet.teacher_id','=','teacher.id')
				->where("sheet.squads_id","=",$v['squad_id'])
				->select([
					"sheet.crouse_id","teacher.username as teacher_name"
				])->get();
			$sheets = json_decode(json_encode($sheets), true);
			foreach($sheets as $sheet){
				$v['teacher_'.$sheet['crouse_id']] = $sheet['teacher_name'];
			}
			$v['total'] = DB::table('students')->where('squad_id','=',$v['squad_id'])->count();
			$v['lost'] = DB::table("applys")
				->whereIn('type',["2","3"])
				->whereBetween('sp_time',$date_arr)
				->where([
					['status','=','1'],
					['squad_id','=',$v['squad_id']]
				])->count();
		}
		return ['code'=>1,'msg'=>"ok","data"=>[
			'table'=>$data,
			'total' => sizeof($lost_squad_ids)
		]];
	}

	/**
	 * [获取流失详情]
	 */
	public function getTodayLossDetails(Request $request) {
		$data = DB::table('applys as a')
				->join('students as st','st.id','=','a.student_id')
				->where([
					['a.sp_time','>',date("Y-m-d")],
					['a.squad_id','=',$request->get('squad_id')]
				])
				->whereIn('a.type',['2','3'])
				->select([
					'st.en_name as st_name','a.sp_time','a.study_jindu','a.week','a.memo'
				])
				->get();
		return ['code'=>1,'msg'=>'ok','data'=>$data];
	}


	/**
	 * [班级追踪]
	 */
	public function track($ids) {
		$ids = explode(",",$ids);
		$ids = array_merge(array_filter(array_unique($ids)));
		$ids = implode(",",$ids);
		return view("admin.dashboard.track",["ids" => $ids]);
	}

	/**
	 * [班级追踪表格]
	 */
	public function mkTrackTable(Request $req) {
		$ids = explode(",",$req->get('ids'));
		$ids = array_merge(array_filter(array_unique($ids)));
		$data = [];
		for($i=0;$i<sizeof($ids);$i++){
			$data[$i] = $this->getTrackTable($ids[$i]);
		}
		return ['code'=>1,'msg'=>'ok','data'=>$data];
	}
	private function getTrackTable($id) {
		$data[] = $this->getTrackRow($id);
		if($data[0]){
			$data[0]['lesson'] = $data[0]['title'] = "btn";
			$data[0]['sheet'] = DB::table('crousesheets as sheet')
				->join("crouses as crouse",'sheet.crouse_id','=','crouse.id')
				->join("admin_users as teacher",'sheet.teacher_id','=','teacher.id')
				->where('squads_id','=',$data[0]['id'])
				->select([
					'crouse.name as crouse_name','teacher.username as teacher'
				])->get();
		}
		$data[] = [
			'name' => "家校",
			'teacher' => "作业",
			'level' => "考勤",
			'stage' => "听写",
			'start' => "测试",
			'lesson' => "班主任录入"
		];
		$data[] = [
			'name' => "准交|均分",
			'teacher' => "准交|均分",
			'level' => "出勤率",
			'stage' => "参与度|均分",
			'start' => "参与度|均分",
			'lesson' => "异常次数"
		];
		for($i = 0; $i < 3;$i++){
			$week = $this->term-3-$i;
			$data[$i+3] = [
				'title' => "第".$week."周",
				'name' => "popover",
				'teacher' => "popover",
				'level' => "popover",
				'stage' => 'popover',
				'start' => 'popover',
				'lesson' => 'popover',
				'squad_id' => $data[0]['id'],
				'week_index' => $week,
			];
			$s_time = date("Y-m-d H:i:s",$this->start_timestamp + (24*3600*7)*($this->term-3-$i));
			$e_time = date("Y-m-d H:i:s",(strtotime($s_time) + 7 * 24 * 3600));
			$where = [
				["squad_id","=",$data[0]['id']],
				["create_time",">",$this->start_date],
				["week","=", $week]
			];
			// 家校
			$jx_rate = DB::table('squad_month')->where($where)->value('jx_rate');
			$jx_avg = DB::table('squad_month')->where($where)->value('jx_avg');
			if(!$jx_avg){
				$jx_avg = 0;
			}
			$data[$i+3]['popover0'] = (round($jx_rate,4)*100)."%" . " | " .$jx_avg;

			// 作业
			$zy_rate = DB::table('squad_month')->where($where)->value('zy_rate');
			$zy_avg = DB::table('squad_month')->where($where)->value('zy_avg');
			if(!$zy_avg){
				$zy_avg = 0;
			}
			$data[$i+3]['popover1'] = (round($zy_rate,4)*100)."%" . " | " . $zy_avg;

			// 出勤
			$kq_rate = DB::table('squad_month')->where($where)->value('kq_rate');
			$data[$i+3]['popover2'] = (round($kq_rate,4)*100)."%";

			// 听写
			$dictation_rate = DB::table('squad_month')->where($where)->value('dictation_rate');
			$dictation_avg = DB::table('squad_month')->where($where)->value('dictation_avg');
			if(!$dictation_avg){
				$dictation_avg = 0;
			}
			$data[$i+3]['popover3'] = ($dictation_rate*100)."%" . " | " . $dictation_avg;

			// 测试
			$test_rate = DB::table('squad_month')->where($where)->value('test_rate');
			$test_avg = DB::table('squad_month')->where($where)->value('test_avg');
			if(!$test_avg){
				$test_avg = 0;
			}
			$data[$i+3]['popover4'] = ($test_rate*100)."%" . " | " . $test_avg;
			$tutor_id = DB::table("squads")->where('id','=',$data[0]['id'])->value("class_teacher");
			// 异常次数
			$notice = DB::table("notices")
				->where('tutor_id','=',$tutor_id)
				->whereIn('status',['3','4'])
				->whereBetween('starttime',[$s_time,$e_time])
				->count();
			if($notice == 0){
				$data[$i+3]['lesson'] = "0次";
			}else{
				$data[$i+3]['popover5'] = $notice."次";
			}
		}
		return $data;
	}

	/**
	 * [制作班级追踪hover展开的table]
	 */
	public function mkTrackHoverTable(Request $request){
		$col = $request->get("col");
		$row = $request->get("row");
		$data = [
			['col_0'=>"班段",'col_1'=>''],
			['col_0'=>"班型",'col_1'=>''],
			['col_0'=>"开班段",'col_1'=>''],
			['col_0'=>"阶段",'col_1'=>''],
			['col_0'=>"班主任",'col_1'=>'']
		];
		$squad = DB::table("squads")->where("id",'=',$row['squad_id'])->first();
		$arr_in = [
			DB::table("squads")->where("section",'=',$squad->section)->pluck("id"),
			DB::table("squads")->where("type",'=',$squad->type)->pluck('id'),
			DB::table("squads")->where("batch",'like',explode("-",$squad->batch)[0]."-%")->pluck('id'),
			DB::table("squads")->where("group",'=',$squad->group)->pluck("id"),
			DB::table("squads")->where("class_teacher",'=',$squad->class_teacher)->pluck("id")
		];
		switch ($col) {
			case 'jiaxiao':
				$field_rate = "jx_rate";
				$field_avg = "jx_avg";
				break;
			case 'zuoye':
				$field_rate = "zy_rate";
				$field_avg = "zy_avg";
				break;
			case 'attendance':
				break;
			case 'dictation':
				$field_rate = "dictation_rate";
				$field_avg = "dictation_avg";
				break;
			case 'test':
				$field_rate = "test_rate";
				$field_avg = "test_avg";
				break;
			case 'notices':
				break;
		}
		foreach($arr_in as $k => $in){
			$rate = DB::table("squad_month")->where([
				["create_time",'>',$this->start_date],
				["week","=",$row['week_index']],
				[$field_rate,">",0]
			])->whereIn('squad_id',$in)->avg($field_rate);
			$avg = DB::table("squad_month")->where([
				["create_time",'>',$this->start_date],
				["week","=",$row['week_index']],
				[$field_avg,">",0]
			])->whereIn('squad_id',$in)->avg($field_avg);
			$_rate = DB::table("squad_month")->where([
				["create_time",'>',$this->start_date],
				["week","=",$row['week_index']],
				['squad_id',"=",$row['squad_id']]
			])->value($field_rate);
			$_avg = DB::table("squad_month")->where([
				["create_time",'>',$this->start_date],
				["week","=",$row['week_index']],
				['squad_id',"=",$row['squad_id']]
			])->value($field_avg);
			$rate = number_format($rate,4)*100;
			$avg = number_format($avg,2);
			$_rate = number_format($_rate,4)*100;
			$_avg = number_format($_avg,2);
			$_rate = $_rate - $rate;
			$_avg = $_avg - $avg;
			$data[$k]['col_1'] = [
				"percent" => number_format($rate,2),
				"avg" => number_format($avg,2),
				"_percent" => abs(number_format($_rate,2)),
				"_avg" => abs(number_format($_avg,2))
			];
			if($_rate == 0){
				$data[$k]['col_1']['p'] = "1";
			}else if($_rate > 0){
				$data[$k]['col_1']['p'] = "+1";
			}else{
				$data[$k]['col_1']['p'] = "-1";
			}
			if($_avg == 0){
				$data[$k]['col_1']['a'] = "1";
			}else if($_avg > 0){
				$data[$k]['col_1']['a'] = "+1";
			}else{
				$data[$k]['col_1']['a'] = "-1";
			}
		}
		return ['code'=>1,'msg'=>'ok','data'=>$data];
	}

	/**
	 * [班级对比]
	 */
	public function contrast($ids) {
		$ids = explode(",",$ids);
		$ids = array_merge(array_filter(array_unique($ids)));
		$ids = implode(",",$ids);
		return view("admin.dashboard.contrast",["ids" => $ids]);
	}

	public function mkContrastTable(Request $request) {
		$ids = explode(",",$request->get('ids'));
		$ids = array_merge(array_filter(array_unique($ids)));
		$title = [
			"","班级名","班段","班型","阶段","开班段","班主任","课程","考情次数","近4周平均出勤率",
			"学期平均出勤率","家校次数","近4周平均准交率","学期平均准交率","近4周平均分","学期平均分",
			"作业次数","近4周平均准交率","累计平均准交率","近4周平均分","学期平均分","听写次数","近4周平均分",
			"学期平均分","测试","近4周平均分","学期平均分","班主任学期总异常","班主任近4周异常数"
		];
		$squads = DB::table("squads as squad")
			->join("admin_users as teacher",'squad.class_teacher','=','teacher.id')
			->join("class_types as ct",'squad.type','=','ct.id')
			->whereIn("squad.id",$ids)
			->select([
				'squad.id','squad.full_name','squad.name','teacher.username as tutor',
				'squad.start_year','squad.start_season','ct.name as type','teacher.id as tutor_id'
			])->orderBy('squad.id','asc')->get();
		$squads = json_decode(json_encode($squads),true);
		$data = [];
		foreach($title as $key => $t){
			$data[$key]["title"] = $t;
		}
		$week = [];
		for($i=0;$i<4;$i++){
			$week[] = $this->term - 3 - $i;
		}
		$week_end = date("Y-m-d",($this->term-3)*3600*24*7 + $this->start_timestamp);
		$week_start = date("Y-m-d",($this->term-7)*3600*24*7 + $this->start_timestamp);
		foreach($squads as $k => $v){
			$st_ids = DB::table('students')->where('squad_id','=',$v['id'])->pluck("id");
			$attendance_total = DB::table("attendance_records")->whereIn('student_id',$st_ids)->count();
			$jiaxiao_total = DB::table("jiaxiao_records")->whereIn('student_id',$st_ids)->count();
			$zuoye_total = DB::table("zuoye_records")->whereIn('student_id',$st_ids)->count();
			$dictation_total = DB::table("dictation_score_records")->whereIn('student_id',$st_ids)->count();
			$test_total = DB::table("test_score_records")->whereIn('student_id',$st_ids)->count();
			$data[0][$k] = "btn";
			// 班级信息
			$data[1][$k] = $v['name'];
			$data[2][$k] = explode("(",explode("-",$v['full_name'])[0])[1];
			$data[3][$k] = $v['type'];
			$data[4][$k] = explode(")",explode("-",$v['full_name'])[1])[0];
			$data[5][$k] = $v['start_year']."-".$v['start_season'];
			$data[6][$k] = $v['tutor'];
			$data[7][$k]['btn'] = "lesson";
			$data[7][$k]['lesson'] = DB::table('crousesheets as sheet')
				->join("crouses as crouse",'sheet.crouse_id','=','crouse.id')
				->join("admin_users as teacher",'sheet.teacher_id','=','teacher.id')
				->where('squads_id','=',$v['id'])
				->select([
					'crouse.name as crouse_name','teacher.username as teacher'
				])->get();
			// 考勤
			$data[8][$k] = $attendance_total;
			$data[9][$k] = DB::table('squad_month')
				->whereIn('week',$week)
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('kq_rate');
			$data[9][$k] = (round($data[9][$k],4)*100)."%";
			$data[10][$k] = DB::table('squad_month')
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('kq_rate');
			$data[10][$k] = (round($data[10][$k],4)*100)."%";
			// 家校
			$data[11][$k] = $jiaxiao_total;
			$data[12][$k] = DB::table('squad_month')
				->whereIn('week',$week)
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('jx_rate');
			$data[12][$k] = (round($data[12][$k],4)*100)."%";
			$data[13][$k] = DB::table('squad_month')
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('jx_rate');
			$data[13][$k] = (round($data[13][$k],4)*100)."%";
			$data[14][$k] = round(DB::table('squad_month')
				->whereIn('week',$week)
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('jx_avg'),2);
			$data[15][$k] = round(DB::table('squad_month')
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('jx_avg'),2);
			// 作业
			$data[16][$k] = $zuoye_total;
			$data[17][$k] = DB::table('squad_month')
				->whereIn('week',$week)
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('zy_rate');
			$data[17][$k] = (round($data[17][$k],4)*100)."%";
			$data[18][$k] = DB::table('squad_month')
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('zy_rate');
			$data[18][$k] = (round($data[18][$k],4)*100)."%";
			$data[19][$k] = round(DB::table('squad_month')
				->whereIn('week',$week)
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('zy_avg'),2);
			$data[20][$k] = round(DB::table('squad_month')
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('zy_avg'),2);
			// 听写
			$data[21][$k] = $dictation_total;
			$data[22][$k] = round(DB::table('squad_month')
				->whereIn('week',$week)
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('dictation_avg'),2);
			$data[23][$k] = round(DB::table('squad_month')
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('dictation_avg'),2);
			// 测试
			$data[24][$k] = $test_total;
			$data[25][$k] = round(DB::table('squad_month')
				->whereIn('week',$week)
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('test_avg'),2);
			$data[26][$k] = round(DB::table('squad_month')
				->where('squad_id',$v['id'])
				->where('create_time',">",$this->start_date)
				->avg('test_avg'),2);
			// 异常
			$data[27][$k] = DB::table('notices')
				->whereIn("status",['3','4'])
				->where("starttime",">",$this->start_date)
				->where('tutor_id',$v['tutor_id'])
				->count();
			$data[28][$k] = DB::table('notices')
				->whereIn("status",['3','4'])
				->whereBetween('starttime',[$week_start,$week_end])
				->where('tutor_id',$v['tutor_id'])
				->count();
		}
		return ["code"=>1,'msg'=>'ok','data'=>$data];
	}
}
